Last Update: August 19, 2022
DDAS BASICS
Table of Contents
Functions to Execute When the DDAS Entry Is .t. TRUE
Understanding Conditional Statements
Working with Processing Options
Preventing Multiple DDAS Triggers
Preventing DDAS from Occurring
Using Groups to Categorize DDAS Objects
Add/Redate a Task in Relation to a Date Field
Update up to 3 Additional Fields
Notes about Parenthesis and OOP
Notes on Characters, Dates, Numbers and Memos
Summary
DDAS stands for Data Driven Automation System. It is designed to automate certain field changes and actions within cases. Through conditional formatting, DDAS can automate specified actions, ensuring that the desired changes occur only under the given conditions. DDAS is programmed to support two automation options. DDAS can be triggered by a manual entry-based trigger or an action-based trigger. When the specified field is manually entered, or the specified action is executed, DDAS can then perform the specified function. Both automated options have limitations which are discussed below.
DDAS Supported Databases
There are several databases that can be used within a DDAS condition, essentially any table that is used by the system, a rule is triggered. One caveat is when testing conditions within DDAS, not every table will be opened. Testing the rule live within a test file is the best way to indicate whether the rule is configured correctly or not.
Not all databases and fields are supported by DDAS. However, there are many databases that are supported. Below are the most common databases. If the database the firm would like to use is not seen in this table, please feel free to test it or contact the support staff at Q-Soft to inquire.
| Main Supported Databases | |||||
| Debt | Dlegal | Special | Caseptp | Court | Dname |
| Deceased | Dgarn | Staterule | Upatient | Dserve | ATT (Receiver) |
| Plaintif | Arbitrate | Cust | Foreclosure | Judge | Sheriff |
A Manual Entry Based Trigger
A manual entry-based trigger is when a user manually types a character into a field in an account, and a DDAS object specifies that database and field name. For example, if a DDAS object specified DLEGAL and CASENO, and a user went into the Legal tab and updated the CASENO field, the DDAS object would trigger. However, there are several fields in Q-LawE that are typically not manually entered such as the SUIT AMOUNT field. This field is normally populated when the [record a suit] button is clicked when entering the suit information. This is an action-based trigger (discussed below); therefore, this database and field combination is not monitored by DDAS and will not work as a trigger for DDAS.
Action Based Trigger
An action-based trigger is a trigger that activates a DDAS object by doing an action in Q-LawE such as scanning a document or filing suit. These action-based triggers are typically particular buttons being pressed during a process. The action of pressing these buttons is what triggers DDAS. Because there is no database and fieldname that is manually updated, we needed a different way of activating DDAS. When setting up an action-based trigger, the database will always be set to “SYSTEM” and the field name will be the name given for that action. See Appendix A for a list of the various actions.
DDAS Capabilities
It is important to understand what changes can be made because of a manual entry trigger or action-based trigger. Or in other words, what DDAS can “do”. DDAS allows the user several options. These options are based on either the field changing or the action taking place as outlined above. There are multiple functions that can be used within DDAS to allow the user flexibility in the automation process. For example, the user will be able to assign DDAS to add a task, re-date a task, show a reminder for the user, and assign the case to another desk. Even though there are lots of possibilities for automation when it comes to creating DDAS functions, there are still a limited number of automated options supported at this time. If a field is changed, or an action is performed, then DDAS can be programmed to do what is in Appendix B.
The DDAS Screen
To access the DDAS screen, from the Main Menu in Q-LawE, navigate to the Maintenance Menu, the choose [Setup DDAS]. This will display the Setup Data Drive Automation System Entries screen that will list any DDAS items. Double click an item or choose [Select Record]. Then choose [Add duplicate] to add a new DDAS item.
DDAS Entry Screen
Above is the detail window for DDAS entries. The various fields are populated based on the user’s desired system reaction to data changes. Below is a breakdown of the fields:
Group
Group is similar to Doc Template groups, or Document folders. It is designed solely to allow the user to group similar DDAS types together to make it easier to find and edit them. It is strictly designed to help filter the rules. Custom groups can be manually typed in this field or selected from the drop down.
These two fields are the trigger. They are used to indicate which field or action should be monitored.
Any field that is entered will have a database first and then the field next. For example, dlegal.caseno will be DLEGAL for the database and CASENO for the field.
Any action that is monitored has a specific “database” (typically SYSTEM) and then the “field” (typically the respective action NAME).
The completion of a task as a trigger uses “TASK” for the database and the task “CODE” for the field. The task code must be the exact code as entered in the Task code manager.
Desc
This is for a short description of the DDAS item. This description is shown on the previous screen and will help the user understand what the function of this DDAS item is. It is also to help categorize the items so when searching, it can help identify the appropriate DDAS item. This is solely for categorizing and giving a brief description only. There is no programming function tied to this field.
Check this to activate the item. If this box is not checked, this DDAS item will be turned off and will not run. Make sure this is checked after thoroughly testing the DDAS item first. This immediately activates the item system wide.
Process Sort Order
The sort order has 2 functions: 1) to set a run priority for DDAS objects that have the same trigger (database and field) and 2) to add a unique number for each DDAS object with the same trigger. This will allow the user to use this sort number to help differentiate several very similar DDAS objects. This will also inform the system which item to run first if there are similar items.
Condition
This is the “meat” of the DDAS system. The system processes the conditional statements in this field to check if the DDAS item should be executed or not. If the user is setting a condition or conditions, the condition(s) are entered into this field. The database and field initialize the DDAS item, and the condition tells the system whether or not to process the DDAS item.
Test Expression/for Ourfile
Click this to test and verify the condition statement within the Condition box. This will process the expression using the specified ourfile number in the field, and evaluate whether that condition is true or false on that file. If it is not a valid condition with incorrect conditional program strings, it will likely generate an error. Simply ignore the error and check the expression for mistakes. Common mistakes are spelling errors and too many or too few parenthesis.
Functions to Execute When the DDAS Entry Is .t. TRUE
The below items are optional and will take effect if the condition statement in the Condition box has an expression of true. If they are blank, the system will ignore the fields. Some functions can be set to process at the same time.
Add New Task
When a task code is listed in this field, that task will be added to the case when the DDAS item runs.
Number of Days
This field in used in conjunction with the Add New Task and Redate Task fields. If there is a task code in either of these fields, and there is a number in Number of Days field, the task code will either be redated or added with a due date of today’s date + the number in this field.
See Task List
This is used to view the task list to ensure the proper task is chosen and spelled correctly.
Day Calc
The day calc field allows the user to expand a little beyond the number of days field. Using this field, the user can have a task added or redated for a certain number of days before or after another date field.
- The field format should be database.field [+ or -] [# of days]
- Example: dlegal.courtdate + 5
- This will add or redate the chosen task to be due 5 days after the courtdate on file.
Task Complete
If there is a task in this field and is found active in the case when the DDAS item runs, the system will mark it completed.
Task delete
If there is a task in this field and is found active in the case when the DDAS item runs, the system will delete it.
Task Redate
If the task in this field is found active in the case, the system will change the scheduled date by the number of days listed in the # of days field. Or, it will redate the task as specified by the Day Calc field if that function is used.
Show Reminder / Reminder text
When the user performs a task that activates the DDAS, the system can create a pop up notification in the form of a message box. This shows the reminder text, a custom message, and what was changed by the DDAS system. Enter the title of the reminder in the Show reminder field, and enter the custom message in the Reminder text field.
Edi Matrix Code
This field is for the EDI Matrix code to process on the case. This is useful for automatically sending notifications to EDI clients if certain fields are changed. The code must be setup in the EDI matrix for this feature to work.
Move to New Desk
This field allows DDAS to move the file to the specific desk chosen in this field.
File Work Type
Select the new case work type from the drop down menu. This flags the case for the type of work it will be.
Tickler Update/Days
In this field, enter the number of days in the future to schedule the tickler date.
Client Status Code 1 and 2
To update the status codes of a case when DDAS runs, choose the status codes from these drop downs.
Record Note
This function allows the user to choose if a note is to be recorded when DDAS runs.
Recnote
The user must check this box to activate the record note function.
Notetype
Here is where the type of note is set: A = system note, C = Activity/collector note, E = emergency note, L = Legal note and D = docket note, etc.
Notetext
This is where the body of the note is entered. The note text can contain plain text and variable fields to insert data from Q-LawE into the note. For example, typing <> will insert the court date into the note. Variables must be enclosed in angled brackets (<< and >>) and can be any field in the system. Character fields (names, account numbers, descriptions, etc.) should be formatted as follows: <<alltrim(database.field)>>.
Runprog
This provides the system with a substantial amount of customizability and power. For example, a program can be run to generate a special report output to a specific folder, or can change specific dates in the system, delete a number of cases, etc. If the needs of the firm start to be outside the scope of what DDAS can do, a custom program could be used. However, due to the nature of custom programs and custom programming, there may be a fee for any programming Q-Soft is asked to perform. It is NOT recommended that users attempt to build their own custom programs as severe damage to the system can result.
Created
This shows which user created this DDAS item and when.
Edit
This shows the last user to edit the item and when.
Active
This shows which user activated the DDAS item and when.
01 Update Q-LawE Data Fields (And 02 and 03 Data Fields)
Enter the name of the database and field to be updated (debt.dfname). Then in the WITH box, enter the text with which to update the field when DDAS processes. Characters will need to be enclosed in quotes “Text to update field” and dates will need to be enclosed in braces {01/01/2012}. To update a field to be blank, simply place two quotes “” in the WITH box for a character field, and two braces {} in the WITH box for a date field.
- Example: debt.todo WITH “Review Answer”
- Example: dlegal.courtdate WITH {10/02/2012}
Once the DDAS information is chosen, and the data is tested, the DDAS is ready to work in the system. Remember to check the box to activate the DDAS. (To stop the DDAS action from occurring, return to this screen and uncheck the box). The DDAS is automatically saved when the screen is exited. DDAS entries are effective immediately and are system wide.
Understanding Conditional Statements
The user has now learned what the programmed capabilities and limitations of the DDAS system are. Now, the user will learn the last capability of the DDAS system which is a conditional statement. Conditional statements test to see if any changes should be made to the case. Then they can specify which changes are acceptable (optional). The outcome of the statement should always be either true or false. If no additional selection is desired, the conditional statement is always true.
For example, if the DDAS system is watching a field, and this field is changed, the statement is true. If the field is not changed, the statement is false. Any changes specified to occur when the field is changed will take place.
If additional selection is desired, the outcome may be different. For example, if the field is changed AND contains a specified client, then it is true. If the field is changed AND DOES NOT contain that specified client, then it is false. Then, if the field is not changed, it is also false.
Creating a Condition
Follow this outline to create a condition.
- Identify the field and database.
- Find the data within the system to be used for the condition and identify the field and database.
- Choose the criteria.
- Decide what criteria to use in the condition. The criteria must be a definable piece of data that can be either true or false.
- Choose your functions and operators.
- When combining more than one criteria, connectors will need to be used. For a list of functions and operators, refer to Appendix C.
- Remember if a condition will not be entered, “true” will still need to be entered. This is done by typing .t. into the Condition field. Refer to Appendix D for .t. and .f. responses.
- Test the condition within DDAS.
- When finished creating the condition, choose the button [Test Expression]. If the expression is built correctly, it should result in a true or a false. If it does not, a message will say the Condtrue is not valid. Recheck the condition for errors. Keep in mind that the [Test Expression] button is not 100%. There are times when successful conditions result in an invalid response but are actually valid.
- Test the condition on a case.
- Once the condition is completed, turn the DDAS entry on by checking [Activate DDAS] in the top left of the DDAS screen.
Examples of Common Conditions
Here is a list of example conditions. The explanations have been omitted for these as it is a guide to use when formatting.
- Debt.balance >= 100
- not qempty(dlegal.suitdate) AND dlegal.ct_state = “KY”
- inlist(dlegal.ctcalcode, ‘CS3’, ‘BLAH’, ‘ME2’) AND not qempty(dlegal.suitdate) and not qempty(dlegal.caseno)
- debt.statid = “160” AND not qempty(dlegal.suitdate) AND not qempty(dlegal.courtdate) AND not qempty(dlegal.courttime) AND dlegal.ct_state = “MO”
- not qempty(dlegal.courtdate) AND BETWEEN((dlegal.courtdate – date()), 1,10)
- NOT INLIST(debt.remark, ‘BANKRUPTCY CHAPTER 7’, ‘BANKRUPTCY CHAPTER 11’, ‘BANKRUPTCY CHAPTER 13’) AND debt.alertatt = .t.
- inlist(dlegal.ctcalcode, ‘CS3’, ‘BLAH’, ‘ME2’) AND not qempty(dlegal.suitdate) and not qempty(dlegal.caseno)
Duplicate Entries
Duplicate DDAS entries are sometimes necessary. For example, if client ABC needs status code E100 to be sent to them when judgment is entered, but client DEF needs status code E101 sent to them when judgment is entered, duplicate DDAS entries will be needed. This is because a condition cannot be written to distinguish between the two.
To create a duplicate entry, choose [Add Dupli] when inside the DDAS item.
Stacking Entries
“Stacking” in DDAS terms occurs when one DDAS entry is set to trigger from something another DDAS entry has just done. Use caution when stacking multiple DDAS entries together.
Depending on the setup, some strings will work flawlessly. Other strings will not process all of the options selected. DDAS stacking will not trigger from using the data fields. It has been designed not to trigger from those fields to prevent loops. When stacking functions together, proper and thorough testing should be done to ensure accuracy and to prevent any looping. “Looping” occurs when one DDAS triggers another DDAS and that DDAS triggers the DDAS that started the action. Completing a task in a group of stacked DDAS items is another action that can stop the group of stacked DDAS entries from continuing.
Working with Processing Options
Only two processing options should be used during a build custom list or a clipboard list. These two options are completing tasks and updating data fields. When a task is completed or a field is updated from here, DDAS entries will complete successfully.
Preventing Multiple DDAS Triggers
The first time the field is filled, DDAS will trigger. Then, the user changes the field again, so DDAS triggers again. DDAS will continue to trigger every time the field is changed unless a specific condition is entered to prevent this from taking place. If a user wants to trigger a DDAS entry only when a blank field is filled, the following condition should be used:
- iif(qempty(mxyoldval), .t., .f.)
- qempty(mxyoldval) This is used for a character field.
- qempty(ctod(mxyoldval)) This is used for a date field.
- qempty(str(mxyoldval,12,2)) This is used for a numeric field.
Preventing DDAS from Occurring
It is also important to know that the user can update fields without triggering DDAS entries, even though the DDAS entry is still activated. The user must do this through the desk work and selecting update data records. The fields can be changed using this process and they will not trigger any active DDAS entries.
Troubleshooting
If your DDAS entry does not activate, check the following:
Supported Database
Check the database being used. Contact Q-Soft to inquire if the database being used is supported. If a non-supported database (or a field from a non-supported database) is entered into DDAS, the entry will not complete. Unfortunately, DDAS allows the entered information to be saved without an error notice appearing. Therefore, it may not be apparent that the database or field being used is not supported.
Spelling
Check the spelling of the database and field as well as the information within the condition. Also check that the task name, client code, addresses etc., are entered exactly as the system has them.
For example: PO Box = PO Box, not P.O. Box and (800) 555-5555 = (800) 555-5555 not 800-555-5555 and task code FLUP does not equal FLUUP or FLUP1
Capitalization
Use the UPPER() command in the condition field if necessary. Remember, John = John but does not = JOHN unless the UPPER() command is used.
Test the Condition
The condition needs to return a true or false. Otherwise it is invalid and will not work. The best test is live on a file to ensure the DDAS item reacts how expected.
Stacking / Looping
Check that DDAS multiple entries have not been stacked or looped.
Managing your DDAS objects
There are several ways to manage the potentially long list of DDAS objects that have been created. Some of the tools available are listed here:
- Using groups to categorize
- Using the sort order to keep the objects in order
- Active DDAS report
Using Groups to Categorize DDAS Objects
When there are multiple DDAS objects that represent different options for the same trigger, or if there are multiple triggers that perform a common function, grouping the objects becomes an important way of organizing the different objects. Upon entering the DDAS screen, the very left hand column in the grid comprises of a group category.
Main DDAS Screen
Appendix A
DDAS Action Triggers
| DATABASE | FIELD | NOTE DESCRIPTION | CALLED FROM |
| SYSTEM | AODCOVER | AODRESULT | AOD Import |
| SYSTEM | AODRESULT | AODRESULT | AOD Import |
| SYSTEM | ARBAWARD | ARBITRATION AWARD RECORDED | ArbitrationAwrd button in legal screen |
| SYSTEM | CERTMAIL | CERTMAIL – RETURN UPDATE | Certified Mail in maintenance |
| SYSTEM | CLOSE | CLOSE CASE | Close screen |
| SYSTEM | COSTPAY | COST CHECK | Disbursements |
| SYSTEM | COSTREC | COST RECEIVED | Client cost received, trx = 11 |
| SYSTEM | DCALAOD | DCAL ASSIGN AOD COUNSEL | CtCal button in legal screen, Add AOD button |
| SYSTEM | DCALATTY | DCAL ASSIGN ATTORNEY | CtCal button in legal screen, Select Attorney |
| SYSTEM | DCALDELETE | DCAL DELETE ENTRY ATTORNEY | CtCal button in legal screen, Delete button |
| SYSTEM | DCALLOCC | DCAL ASSIGN LOCAL COUNSEL | CtCal button in legal screen, Add Local button |
| SYSTEM | DCALNEW | DCAL ADD NEW | Add to Calendar button in legal screen |
| SYSTEM | DIALER | DIALER Result | Dialer import |
| SYSTEM | DOCBATCH | DDAS DOC IMPORT | Doc import |
| SYSTEM | DOCUNEW | DDAS New document | Prepare a new doc screen – Start Word/WP button |
| SYSTEM | DRAGDROP | DDAS DOC DRAGDROP | Drag and drop |
| SYSTEM | GARNISH | GARNISHMENT RECORDED | Record garnishment button in legal screen |
| SYSTEM | JUDGMENT | JUDGMENT RECORDED | Record judgment button in legal screen |
| SYSTEM | LEGALPREP | LEGAL PREP | DEF button in legal screen |
| SYSTEM | MILSTS | TASK DDAS | Military affidavit return |
| SYSTEM | NEWCASE | NEWCASE DDAS | New case either by edi import or manual entry |
| SYSTEM | PAYMENT | DDAS PAYMNT | Payment posted |
| SYSTEM | PAYREVERSE | DDAS PAYMNT REVERSAL | Payment reversed |
| SYSTEM | PHONEAGAIN | SET AS RESCHEDULE FOR ANOTHER PHONE CALL | Call again button in collector work screen |
| SYSTEM | PHONECA | RECORD PHONE CALL | Record call button in collector work screen |
| SYSTEM | PHONEHOT | SET AS HOT ACCOUNT | Hot account button in collector work screen |
| SYSTEM | PROMISECAN | PROMISE To PAY CANCELLED | Delete current button |
| SYSTEM | PROMISEDUE | PROMISE To PAY next due date changed | Next due date changed |
| SYSTEM | PROMISEPAY | PROMISE To PAY RECORDED | Pay Arr screen |
| SYSTEM | REOPEN | CLOSE CASE | Close screen |
| SYSTEM | SCANBATCH | DDAS SCANNING | Scan new docs screen |
| SYSTEM | SERVEX | SERVEX – SERVED SUCCESFUL | Servex – Serve status to dserve.status |
| SYSTEM | STIPULATE | JUDGMENT STIPULATION RECORDED | Stipulation button in legal screen |
| SYSTEM | SUITFILE | SUIT IS FILED | Prep suit button – record suit |
| SYSTEM | TRXDELETE | DDAS TRX DELETE | Deleting an acounting transaction |
| SYSTEM | VENDORIN | DDAS VENDOR IMPORT | Vendor import |
| SYSTEM | YGCDATA | YGC DATA-31-41 IMPORT | YGC receiver update |
| SYSTEM | YGCPCODE | YGC PCODE IMPORT | YGC maintenance update |
| TASK | [TASKCODE] | TAScode: [TASKCODE]- completed | Check task as Complete |
| SYSTEM | PAYCARD | PAYCARD RECORD RECORDED | After a record is created or edited |
| SYSTEM | DCALDELETE | DCAL DELETE ENTRY ATTORNEY | Triggers off the Delete button – Court Hearing Details |
| SYSTEM | DCALAOD | DCAL ASSIGN AOD COUNSEL | Triggers off Add AOD button – Court Hearing Details |
| SYSTEM | DCALLOCC | DCAL ASSIGN LOCAL COUNSEL | Triggers off of Add Local button – Court Hearing Details |
| SYSTEM | DCALATTY | DCAL ASSIGN ATTORNEY | Triggers off of Select Atty button – Court Hearing Details |
| SYSTEM | DCALNEW | DCAL ADD NEW | Triggers off of Add to Calendar from Legal tab |
Appendix B
What DDAS Can Do
Add a Task
Set DDAS to add a task. The task can also be scheduled for a specified number of days in the future.
Delete a Task
Set DDAS to delete a task.
Complete a Task
Set DDAS to complete a task.
Redate a Task
Set DDAS to redate a task for a specified number of days.
Add/Redate a Task in Relation to a Date Field
Set DDAS to add or redate a task + or – a specified amount of days in relation to a specified date field.
Show a Reminder
Set DDAS to show a reminder. This note can say anything and will appear when DDAS takes place to remind the user of the change.
Record a Note
Set DDAS to record a note. The note type can be selected, e.g., E for emergency. The note field also supports dynamic fields, although they must be typed directly in using a very specific format. This format is as follows: <>. This format must be followed exactly in order to work correctly, e.g., <>
- Example note: Judgment entered <>
Change EDI Matrix Code
Set DDAS to update the EDI code for a client.
Move to a New Desk
Set DDAS to assign the case to a new desk.
File Work Type
Set DDAS to assign a new case work type.
Tickler Update
Set DDAS to update the tickler to a specified number of days in the future.
Client Status Code
Set DDAS to update the client status code.
Update up to 3 Additional Fields
Set DDAS to update up to 3 additional fields. from the following databases: Arbitrate, Caseptp, Deceased, Debt, Dlegal, Foreclosure, or Special. No other database can be updated through DDAS.
Any specific text or characters must be entered with quotes:
- “CODE”
Merge fields are permissible with specific formatting:
- <<alltrim(dname.lname)>>
Dates must be formatted as well:
- {^ 1982-01-15} or {10/02/2012}
Run a Program
Set DDAS to run a program. Only one program is valid at this time which is explained later in this document.
Appendix C
Common Functions
Character Formatting
The below functions are used to either convert data to character values, or to format character values.
STR()
STR() converts numbers to characters.
- Proper Format: STR(field,[number indicating max length], [number indicating max numbers after decimal])
- The two numbers are optional.
- For example: If the following is used: STR(balance,12,2), there could be a maximum of 999999999999.99 for the result. This means the result will return the number and round when necessary to ensure there are no more than 12 digits before the decimal and no more than 2 digits after the decimal.
- Another example: STR(balance) would just convert the number as is.
ALLTRIM()
ALLTRIM() removes any extra spaces from a character field. When a character field is queried, the system will return the data requested, as well as however many spaces there are contained in the field length. ALLTRIM() is used to remove the extra spaces so it fits in neatly with other data/text in a report. For example, if a query pulls the Net Account # (a 30 character field) without using ALLTRIM(), and the Net Account # is only 8 digits, the query will return the 8 digits as well as the 22 spaces following the 8 digits: 12345678______________________
- ALLTRIM() can only be used with characters, not numbers. Functions which produce character data, such as the STR() or DTOC() functions, can be used within ALLTRIM().
- Example: ALLTRIM(str(debt.balance))
- Example 2: ALLTRIM(dname.name)
DTOC()
DTOC() literally means “Date to Character” and it converts fields from the date format to a character format. The formatting of the date remains unchanged.
Date Functions
The below values are used to return date values.
CTOD()
CTOD literally means “Character to Date” and it converts fields from the character format to a date format. This function is used primarily when working with the WORDMERG table (which contains the base fields used in Word merging), as all data stored in that field is Character.
DATE()
The DATE() function returns the current date.
Numeric Functions
The below function is used to return numeric values.
VAL()
VAL() converts characters to numbers. This function is used primarily when working with the WORDMERG table (which contains the base fields used in Word merging). The contents of this table are all character values, so when doing math, the data must be converted to numbers first.
Field Evaluators
The below functions are used to check data within a field.
QEMPTY()
QEMPTY() checks to see if a field has data within it. If it does not, it returns “true”.
LEN()
LEN() returns the length of a CHARACTER field. This function should always be used with ALLTRIM(). Below is an example:
- LEN(ALLTRIM(debt.ourfile)) = 5
Criteria Selection
The below functions are used to select pieces of data from various fields, or based on certain expressions.
INLIST()
This function quite literally means “Is in the following list.” Its format is as follows:
- INLIST(field, [value1], [value2], [value3], …)
There can be a maximum of 24 values within one INLIST() function, and it is possible to chain multiple INLIST() functions together using the OR command.
INLIST() is commonly used as a substitute for multiple OR commands on the same field. So, instead of:
- debt.custid = “C1000” OR debt.custid = “C1001” OR debt.custid = “C1002” OR debt.custid = “C1003”
The following INLIST() can be used:
- INLIST(debt.custid, “C1000”, “C1001”, “C1002”, “C1003”)
IIF()
This function literally translates to “Inline IF function” and is formatted as follows:
- IIF(expression, [result if true], [result if false])
Reading left to right, it would translate “If the following expression is true, do this, if not, do this.” IIF statements are incredibly powerful and can return numbers, characters, dates, and can also be nested. When returning values, both the “True” and “False” results must be the same type. It cannot return a character if true, then a number if false. Below are a couple examples of IIF() statements.
- IIF(debt.custid = “C1000” and debt.balance < 500, “The balance is low”, “The balance is high”)
- IIF(debt.custid = “C1000″ and debt.balance < 500, IIF(qempty(dlegal.judgdate),”The balance is low”, “This file has a judgment”),”The balance is high”)
BETWEEN()
BETWEEN() literally checks for a field to be between, and including, two values. It’s formatted as follows:
- BETWEEN(field, [VALUE1], [VALUE2])
BETWEEN() can be used on all data types, but it is important to note the oddities of character values when using this function. For example, when selecting a number of files between 5000 and 6000, it may make sense to try the below string:
- BETWEEN(debt.ourfile, “5000”, “6000”)
However, because 6, 60, and 600 come between 5000 and 6000 when dealing with characters, the results will be skewed. (Please see Appendix D: Notes on Characters and Numbers for more detail.) This is where LEN() comes in help. The string below will yield the proper results. The number at the end of the LEN string (the 4), should be the number of characters used in each of the BETWEEN values (not including quotes). So, since 5000, and 6000 are both 4 characters long, 4 is used at the end of the LEN() string.
- BETWEEN(debt.ourfile, “5000”, “6000”) AND LEN(ALLTRIM(debt.ourfile)) = 4
CONTACTSEEK()
CONTACTSEEK() is a special function used to return specific pieces of data from a contact record. Below is the proper usage:
- CONTACTSEEK(“[contact type]”,”[contact number]”,”[field]”)
When returning the Zip code of the primary debtor’s employer, the following will be used:
- CONTACTSEEK(“POE”,”1″,”ZIP”)
XCONTACTSEEK()
This function is similar to CONTACTSEEK, but can be used in reporting. It is formatted as follows:
- PADR(XCONTACTSEEK(debt.ourfile,”[contact type]”,”[contact number]”,”[field]”),[max number of characters to return])
The debt.ourfile portion never changes, and the [max number of characters to return] should be a number saying the most characters to use from the field. So, if you wanted to use XCONTACTSEEK to return the Zip code of the primary debtor’s employer, you would use the following:
- PADR(XCONTACTSEEK(debt.ourfile,”POE”,”1″,”ZIP”),15)
If you wanted to return the name of the place of employment, you would use the following. Note the larger number:
- PADR(XCONTACTSEEK(debt.ourfile,”POE”,”1″,”NAME”),50)
The “Is Contained Within” Operator ( $ )
This particular operator is pretty handy when trying to find information that may be buried within a character field. For example, say you have a number of files where there is a specific phrase in a note you need find, the $ operator can come in handy. The usage, and an example, are below.
- Usage: [Text to search FOR] $ [Field to search in]
Note that this particular operator is backwards when compared to the other operators listed further below.
- Example: “Was billed on 02/08/2010” $ dnote.dnote
Connectors
Connectors are words and symbols used to connect multiple functions or commands together to make a full string.
AND
AND is an explicit connector. It means both the conditions preceding and following the AND must be true. So, for example, in order for the following command to be true:
- debt.custid=”C1000″ AND debt.balance > 500
The client code on the file MUST be C1000 and the balance MUST be greater than 500.
OR
OR is an optional connector. It means either the condition preceding or following the OR can be true. So, using a similar example as above:
- debt.custid=”C1000″ OR debt.balance > 500
In order for that to be true, either the client code must be C1000, or the balance must be greater than 500. If either is true, the condition is true.
NOT
This function returns the opposite result for the condition immediately following it. For example:
- NOT qempty(CDATE)
The above would return files that did NOT have an qempty closed date field
Operators
Operators is a fancy name for the basic mathematical signs (+, -, =, *, /) and are all operators. Below is a quick rundown of each, as well as a few other useful ones.
The Plus Sign ( + )
When used with numbers, the plus sign is just like basic math. When used with characters, it is used to combine the fields before and after it. Typically, this is used when putting names together, as in the below example.
- ALLTRIM(contactseek(“DEB”,”1″,”LNAME”)) + “, ” + ALLTRIM(contactseek(“DEB”,”1″,”FNAME”))
The above will return: DOE, JOHN
The Minus Sign ( – )
This is only used in mathematical situations, and is for subtraction. It is also used when calculated dates.
- Date() – 45
- This will yield the date that is 45 days before today.
The Multiplication Sign ( * )
This particular symbol, and the next, can be unfamiliar to people who don’t do much math work on a computer. Instead of using an X for multiplication, the asterisk ( * ) is used. This is only used in mathematical situations, and is for multiplication.
The Division Sign ( / )
This is a FORWARD slash, and is the slash located under the question mark on the keyboard. This is only used in mathematical situations, and is for division.
The Greater Than Sign ( > )
This is frequently used to compare amounts and dates. This is a non-inclusive operator. So, using the below example:
- debt.loandate > {^2010-01-01}
The field, debt.loandate, would have to be 1/02/2010 or later for the result to be true.
The Less Than Sign ( < )
This is the opposite of the above sign, the usage is the same.
The Equal Sign ( = )
This is used to compare any number of values, and can be used together with the Greater Than and Less Than signs to make them inclusive. The usage of this symbol is pretty straight forward, when looking for two values to match, use the Equal Sign.
The Does Not Equal Sign ( <> )
Putting the less than, and greater than signs back to back tells the system that the values before and after these carrots are NOT to match. This can be used for dates, numbers, and characters.
Task Operators
iftask
This function is used when a condition is looking to see if a specific task has ever been on the file. The different options are below in a chart using a task named 900.
| iftask(dfile, “900”, “F”) = .t. | Has the task active on the file |
| iftask(dfile, “900”, “F”) = .f. | Does not have the task active on the file |
| iftask(dfile, “900”, “T”) = .t. | Has task completed on the file |
| iftask(dfile, “900”, “T”) = .f. | Does not have the task completed on the file |
| iftask(dfile, “900”) = .t. | Has the task on the file whether completed or active |
| iftask(dfile, “900”) = .f. | Does not have the task on the file whether completed or active |
iftaskdue
Appendix D
Notes
Notes about Parenthesis and OOP
Please Excuse My Dear Aunt Sally, does that phrase ring any bells? If not, it’s the mnemonic device commonly used to explain Order of Operations (OOP). Basically, it explains which parts of a mathematical equation to calculate first. In order, what is within the Parenthesis are calculated first (OOP applies within the parenthesis as well), then Exponents, Multiplication, Division, Addition and finally Subtraction. It is important to remember this because computers are basically glorified calculators, so they obey OOP when building functions.
- This knowledge can come in very handy when building complex conditions. For example, if a group of data needs to be omitted, instead of using the below:
- NOT debt.custid=”C1000″ and NOT debt.collect=”HA” and NOT debt.balance < 500
- Instead, enclose the values in parenthesis and use a single NOT command.
- NOT (debt.custid = “C1000” and debt.collect = “HA” and debt.balance < 500)
Notes on Capitalization
Q-LawE’s database is CASE SENSITIVE, which means the casing of the data being used in a condition must be the same as the data in a field. So, “JOHN” and “John” will not yield the same matches. Q-LawE typically stores the majority of its data in CAPITAL LETTERS, but in the event it is now known, there is a function to help:
- UPPER(database.field)
This function will simply return the field in all capital letters. So it can be used as such:
- UPPER(dlegal.plaintif) = “JAMES DEAN”
Notes on Characters, Dates, Numbers and Memos
Before beginning to working with the various functions in Q-LawE, it is important to understand the primary data types used throughout Q-LawE. Like most database systems, Q-LawE uses four distinct data types that cannot be interchanged. This means that a character value cannot be stored in a date field, and vice versa. Below are the four basic types, along with their descriptions and some basic examples.
Characters
Characters are the most common field used in Q-LawE. They are used to store alphanumeric data, up to a maximum of 254 characters in length. (Note: most fields in Q-LawE do not utilize this maximum length.) A basic rule of thumb is if there can be anything other than a number in the field (a special character, like a hyphen for instance), the field is a character. Examples of character fields are account numbers, case numbers, zip codes, phone numbers, names, addresses, etc.
When character values are defined in conditions, they must be enclosed in quotes, either single or double. It doesn’t matter which, but consistency must be used within a condition.
- Example: debt.dfname = “Johnny”
Numeric
Numeric, or number fields are also quite common, and are almost exclusively used to store dollar amounts and percentages. These fields are also typically “floating point” (meaning they have a decimal point), which helps make them easier to identify.
When numeric values are defined in conditions, they do not need to be enclosed in anything.
- Example: debt.balance < 500
Date and DateTime
These are probably the most self explanatory. If the field contains a date, it’s a date field. DateTime fields contain both the date, and a time. DateTime fields are not widely used in Q-LawE, and can be treated in a similar fashion to Date fields.
- When dates are defined, they must be formatted as follows: {^YYYY-MM-DD}
- Example: debt.cdate > {^2010-01-01}
When using DateTime fields, the above date format should also be used, but remember, saying {^2001-01-01} with a DateTime field is the same as saying “12:00am on 01/01/2010.”
Memos and PADR()
Memo fields are used to store massive amounts of character data. For example, the notes in Q-LawE are all stored in memo fields. Memos require some special handling when trying to glean information for a report.
Memos are defined in a similar way to characters. When data is stored in a memo field, the actual field in the database is changed to read Memo (empty memo fields read: memo), and the actual data is stored in another file. Because of this, when attempting to display the data from a memo field, it usually is displayed as “Memo.” To actually extract the data from the field, a special command, called PADR() is required. PADR() will take data from a field and “pad” it to a maximum specified length. Because of how this function works, it allows data from a memo to be returned. Below is the proper usage:
- PADR([memo field], [maximum number of characters])
So, an example would be:
- PADR(dnote.dnote, 150)
Notes on Characters and Numbers
It is important to note that Q-LawE does not sort Characters and Numbers the same. While numbers, when sorted, will always be sequential, Characters are treated differently. If the numbers, 1, 2, 3, 100, 200, 300, 1000, 2000, 3000 were stored as characters and sorted, they would be listed as follows: 1, 100, 1000, 2, 200, 2000, 3, 300, 3000. Because of this, when using certain commands on character fields, it may be necessary to nest other functions (typically LEN()) to ensure the proper results are produced.
Booleans (Logical Values)
Boolean means either TRUE or FALSE. Computers operate strictly in a boolean realm (binary, 1 for true, 0 for false). Therefore, it would make sense for many of the below functions to return boolean responses. Typically, it is not necessary to know what booleans are; however, there are certain times when wanting to specifically indicate if a field is TRUE or FALSE. These fields are called LOGICAL fields within Q-LawE, and are typically seen as a check box in Q-LawE. In order to use these fields, the proper true and/or false language must be used:
- TRUE is expressed as: .T.
- FALSE is expressed as: .F.
So, to use the .t. and .f. in an example:
IIF(debt.alertbkr = .T., “This account is in bankruptcy”, “This account is not in bankruptcy”)